package BookManage;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Scanner;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: 强仔
 * Date: 2022-01-16
 * Time: 17:23
 * 如果给我offer，我下周就能入职，可以无限期一直实习下去！！！
 */

 /* 如果close()没有手动调用，不能说一定存在问题，
 对象被GC回收时，持有的资源也就释放了，之所以手动调用close()，是因为GC的时间间隔不可预期
 可能在GC释放之前，大量的申请操作堆积，导致资源耗尽
 */
// TODO 通过这个类完成对book表的增删改查
public class BookDao {

    // TODO 新增图书
    public void addBook(Book book){
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            // 1.与数据库建立连接
            connection = DBUtil.getConnection();
            // 2.构造sql语句
            String sql = "insert into book values(null,?,?,?,?,0)";
            statement = connection.prepareStatement(sql);
            Scanner scanner = new Scanner(System.in);

            statement.setString(1,book.getName());
            statement.setString(2,book.getAuthor());
            statement.setInt(3,book.getPrice());
            statement.setString(4,book.getType());
            // 3.执行sql语句
            int ret = statement.executeUpdate();
            if(ret == 1){
                System.out.println("新增成功");
            }else{
                System.out.println("新增失败");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        //无论是否出现异常都会执行到 finally
        finally {
            // 4.回收资源
            DBUtil.close(connection,statement,null);
        }

    }

    //TODO 查看所有的书
    public List<Book> findBooks(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Book> list = new ArrayList<>();
        try{
            // 1.建立连接
            connection = DBUtil.getConnection();

            //2.构造sql语句
            String sql = "select * from book";
            statement = connection.prepareStatement(sql);

            //3.执行sql语句
            resultSet = statement.executeQuery();

            //4.遍历临时表
            while(resultSet.next()){
                Book book = new Book();
                book.setBookId(resultSet.getInt("bookId"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setPrice(resultSet.getInt("price"));
                book.setType(resultSet.getString("type"));
                book.setBorrowed(resultSet.getInt("isBorrowed") == 1);
                list.add(book);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        finally {
            //5.回收资源
            DBUtil.close(connection,statement,resultSet);
        }
        return list;
    }

    //TODO 根据书名找书
    public List<Book> findsBooksByName(String name){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Book> list = new ArrayList<>();
        try{
            // 1.建立连接
            connection = DBUtil.getConnection();

            //2.构造sql语句
            String sql = "select * from book where name = ? ";
            statement = connection.prepareStatement(sql);
            statement.setString(1,name);

            //3.执行sql语句
            resultSet = statement.executeQuery();

            //4.遍历临时表
            while(resultSet.next()) {
                Book book = new Book();
                book.setBookId(resultSet.getInt("bookId"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setPrice(resultSet.getInt("price"));
                book.setType(resultSet.getString("type"));
                book.setBorrowed(resultSet.getInt("isBorrowed") == 1);
                list.add(book);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        finally {
            //5.回收资源
            DBUtil.close(connection,statement,resultSet);
        }
        return list;
    }

    //TODO 删除图书
    public boolean deleteBook(int bookId){
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            //1.与数据库建立连接
            connection = DBUtil.getConnection();

            //2.构造sql语句
            String sql = "delete from book where bookId = ? ";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,bookId);

            //3.执行sql语句
            int ret = statement.executeUpdate();

            if(ret == 1){
                System.out.println("删除成功");
                return true;
            }else{
                System.out.println("没有此书");
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            //4.回收资源
            DBUtil.close(connection,statement,null);
        }
        System.out.println("因为连接异常，导致删除失败");
        return false;
    }

    // TODO 借书
    //借书
    public boolean borrowBook(int bookId){
        Connection connection = null;
        PreparedStatement statement = null;
        PreparedStatement statement2 = null;
        ResultSet resultSet = null;

        try{
            //1.与数据库建立连接
            connection = DBUtil.getConnection();

            //2.先查询书，注意bookId是自增主键，存在唯一，resultSet只有一个
            String sql = "select * from book where bookId = ? ";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,bookId);

            resultSet = statement.executeQuery();
            if(resultSet.next()){
                if(resultSet.getInt("isBorrowed") == 1){
                    System.out.println("此书已借出");
                    return false;
                }
            }else{
                System.out.println("没有此书");
                return false;
            }

            String str = "Update book set isBorrowed = 1 where bookId = ?";

//          如果这样写的话，第一个sql语句的对象还没调用close就回收了，所以要在新建一个sql对象
//          statement = connection.prepareStatement(str);

            statement2 = connection.prepareStatement(str);
            statement2.setInt(1,bookId);
            int ret = statement2.executeUpdate();
            System.out.println("借阅成功");

        }catch(SQLException e){
            e.printStackTrace();
        }
        finally {
//          DBUtil.close(connection,statement,resultSet);
            if(statement2 != null){
                try {
                    statement2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }


        }
        return false;
    }

    //第二波借书
    public boolean borrowBook(String name){
        List<Book> list = findsBooksByName(name);
        if(list.isEmpty()){
            System.out.println("没有此书");
            return false;
        }
        int bookId = -1;
        for(Book book : list){
            if(!book.getisBorrowed()){
                bookId = book.getBookId();
                break;
            }
        }
        if(bookId == -1){
            System.out.println("此书已都被借出！");
            return false;
        }

        Connection connection = null;
        PreparedStatement statement = null;

        try {
            //1.与数据库建立连接
            connection = DBUtil.getConnection();

            //2.构造sql语句
            String sql = " Update book set isBorrowed = 1 where bookId = ? ";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,bookId);

            //3.执行sql语句
            int ret = statement.executeUpdate();
            System.out.println("借书成功");
            return true;

        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DBUtil.close(connection,statement,null);
        }

        return false;
    }

    //最简单的借书
    public boolean borrowBook2(int bookId){
        Connection connection = null;
        PreparedStatement statement = null;
        try{
            //1.与数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "Update book set isBorrowed = 1 where bookId = ? and isBorrowed = 0";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,bookId);
            //3.执行sql
            int ret = statement.executeUpdate();
            //根据改变的行数判断借书成功与否，但是 不知道是此书不存在，还是已经借出
            if(ret == 0){
                System.out.println("借书失败");
                return false;
            }else{
                System.out.println("借书成功");
                return true;
            }

        }catch(SQLException e){
            e.printStackTrace();
        }
        finally {
            DBUtil.close(connection,statement,null);
        }
        return false;
    }


    // TODO 还书
    public boolean returnBook(int bookId){
        Connection connection = null;
        PreparedStatement statement = null;
        PreparedStatement statement2 = null;
        ResultSet resultSet = null;
        try{
            //1.与数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句（查询）
            String sql = "select * from book where bookId = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,bookId);
            //3.执行sql语句
            resultSet = statement.executeQuery();
            if(resultSet.next()){
                if(resultSet.getInt("isBorrowed") == 1){
                    //构造sql语句（修改）
                    String str = "Update book set isBorrowed = 0 where bookId = ?";
                    statement2 = connection.prepareStatement(str);
                    statement2.setInt(1,bookId);
                    //执行
                    statement2.executeUpdate();
                    System.out.println("还书成功");
                    return true;
                }else{
                    System.out.println("此书已还！！！");
                    return false;
                }
            }else{
                System.out.println("没有此书！！！");
                return false;
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        finally {
            //4.回收资源
            if(statement2 != null){
                try{
                    statement2.close();
                }catch(SQLException e){
                    e.printStackTrace();
                }
            }
            if(resultSet != null){
                try{
                    resultSet.close();
                }catch(SQLException e){
                    e.printStackTrace();
                }
            }
            if(statement != null){
                try{
                    statement.close();
                }catch(SQLException e){
                    e.printStackTrace();
                }
            }
            if(connection != null){
                try{
                    connection.close();
                }catch(SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return false;
    }

    //还书
    public boolean returnBook2(int bookId){
        Connection connection = null;
        PreparedStatement statement = null;

        try{
            connection = DBUtil.getConnection();
            String sql = "Update book set isBorrowed = 0 where bookId = ? and isBorrowed = 1";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,bookId);
            int ret = statement.executeUpdate();
            if(ret == 0){
                System.out.println("无法还书");
                return false;
            }else{
                System.out.println("还书成功");
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,null);
        }
        return false;
    }




//    //测试
//    public static void main(String[] args) {
//        BookDao bookDao = new BookDao();
//        //插入书籍
//        Book book = new Book();
//        book.setBookId(1);
//        book.setName("史记");
//        book.setAuthor("司马迁");
//        book.setPrice(10000);
//        book.setType("史书");
//        bookDao.addBook(book);
//
//        //借书
//        bookDao.borrowBook("小郭同学");
//
//
//        //还书
//        bookDao.returnBook(4);
//
//
//        //删书
//        bookDao.deleteBook(4);
//
//
//        //查看所有的书
//        List<Book> list = bookDao.findBooks();
//        for (Book book1 : list) {
//            System.out.println(book1);
//        }
//
//
//        //根据名字找书
//        List<Book> list2 = bookDao.findsBooksByName("西游记");
//        for (Book book2 : list2) {
//            System.out.println(book2);
//        }
//
//        //测试用户
//        UserDao userDao = new UserDao();
//        User user = userDao.findUser("玄烨");
//        System.out.println(user);
//    }
}
